package com.chengh.vhr.common.utils;

import com.chengh.vhr.model.Employee;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * 导出员工资料工具类
 * ClassName: POIUtils
 * Package: com.chengh.vhr.common.utils
 * Datetime: 2021/1/31 20:56
 * Author: chengH
 */
public class POIUtils {
    public static ResponseEntity<byte[]> employee2Excel(List<Employee> list) {
        // 1.创建一个Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 2.创建文档摘要
        workbook.createInformationProperties();
        // 3.获取并配置文档信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        // 文档类型
        docInfo.setCategory("员工信息");
        // 管理员
        docInfo.setManager("程航");
        // 公司
        docInfo.setCompany("com.chengh.vhr");
        // 4.获取并配置文档摘要信息
        SummaryInformation summInfo = workbook.getSummaryInformation();
        // 文档标题
        summInfo.setTitle("员工信息表");
        // 文档作者
        summInfo.setAuthor("chengh");
        // 备注信息
        summInfo.setComments("本文档由 chengh 提供");

        // 5.创建一个表单(sheet)
        HSSFSheet sheet = workbook.createSheet("员工信息表");
        // 设置每一列的宽度
        sheet.setColumnWidth(0,10*256);
        sheet.setColumnWidth(1,15*256);
        sheet.setColumnWidth(2,15*256);
        sheet.setColumnWidth(3,15*256);
        sheet.setColumnWidth(4,15*256);
        sheet.setColumnWidth(5,15*256);
        sheet.setColumnWidth(6,15*256);
        sheet.setColumnWidth(7,15*256);
        sheet.setColumnWidth(8,15*256);
        sheet.setColumnWidth(9,15*256);
        sheet.setColumnWidth(10,15*256);
        sheet.setColumnWidth(11,12*256);
        sheet.setColumnWidth(12,12*256);
        sheet.setColumnWidth(13,15*256);
        sheet.setColumnWidth(14,15*256);
        sheet.setColumnWidth(15,15*256);
        sheet.setColumnWidth(16,15*256);
        sheet.setColumnWidth(17,15*256);
        sheet.setColumnWidth(18,15*256);
        sheet.setColumnWidth(19,15*256);
        sheet.setColumnWidth(20,15*256);
        sheet.setColumnWidth(21,15*256);
        sheet.setColumnWidth(22,15*256);
        sheet.setColumnWidth(23,15*256);


        // 6.创建样式
        // 创建标题行的样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 设置背景颜色
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        // 设置填充样式
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 创建日期列样式
        HSSFCellStyle dateCellStyle = workbook.createCellStyle();
        // 类似yyyy-MM-dd
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));


        // 7.创建标题行,参数0代表第一行
        HSSFRow r0 = sheet.createRow(0);
        // 创建列,参数0代表第一列
        HSSFCell c0= r0.createCell(0);
        // 设置值
        c0.setCellValue("编号");
        c0.setCellStyle(headerStyle);

        HSSFCell c1 = r0.createCell(1);
        c1.setCellValue("姓名");
        c1.setCellStyle(headerStyle);

        HSSFCell c2 = r0.createCell(2);
        c2.setCellValue("工号");
        c2.setCellStyle(headerStyle);

        HSSFCell c3 = r0.createCell(3);
        c3.setCellValue("出生日期");
        c3.setCellStyle(headerStyle);

        HSSFCell c4 = r0.createCell(4);
        c4.setCellValue("证件号码");
        c4.setCellStyle(headerStyle);

        HSSFCell c5 = r0.createCell(5);
        c5.setCellValue("性别");
        c5.setCellStyle(headerStyle);

        HSSFCell c6 = r0.createCell(6);
        c6.setCellValue("婚姻状况");
        c6.setCellStyle(headerStyle);

        HSSFCell c7 = r0.createCell(7);
        c7.setCellValue("名族");
        c7.setCellStyle(headerStyle);

        HSSFCell c8 = r0.createCell(8);
        c8.setCellValue("籍贯");
        c8.setCellStyle(headerStyle);

        HSSFCell c9 = r0.createCell(9);
        c9.setCellValue("政治面貌");
        c9.setCellStyle(headerStyle);

        HSSFCell c10 = r0.createCell(10);
        c10.setCellValue("电子邮箱");
        c10.setCellStyle(headerStyle);

        HSSFCell c11 = r0.createCell(11);
        c11.setCellValue("电话号码");
        c11.setCellStyle(headerStyle);

        HSSFCell c12 = r0.createCell(12);
        c12.setCellValue("联系地址");
        c12.setCellStyle(headerStyle);

        HSSFCell c13 = r0.createCell(13);
        c13.setCellValue("所属部门");
        c13.setCellStyle(headerStyle);

        HSSFCell c14 = r0.createCell(14);
        c14.setCellValue("职位");
        c14.setCellStyle(headerStyle);

        HSSFCell c15 = r0.createCell(15);
        c15.setCellValue("职称");
        c15.setCellStyle(headerStyle);

        HSSFCell c16 = r0.createCell(16);
        c16.setCellValue("入职日期");
        c16.setCellStyle(headerStyle);

        HSSFCell c17 = r0.createCell(17);
        c17.setCellValue("转正日期");
        c17.setCellStyle(headerStyle);

        HSSFCell c18 = r0.createCell(18);
        c18.setCellValue("合同起始日期");
        c18.setCellStyle(headerStyle);

        HSSFCell c19 = r0.createCell(19);
        c19.setCellValue("合同截止日期");
        c19.setCellStyle(headerStyle);

        HSSFCell c20 = r0.createCell(20);
        c20.setCellValue("合同期限");
        c20.setCellStyle(headerStyle);

        HSSFCell c21 = r0.createCell(21);
        c21.setCellValue("最高学历");
        c21.setCellStyle(headerStyle);

        HSSFCell c22 = r0.createCell(22);
        c22.setCellValue("毕业院校");
        c22.setCellStyle(headerStyle);

        HSSFCell c23 = r0.createCell(23);
        c23.setCellValue("所属专业");
        c23.setCellStyle(headerStyle);

        for (int i= 0; i < list.size(); i++) {
            Employee employee = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(employee.getId());
            row.createCell(1).setCellValue(employee.getName());
            row.createCell(2).setCellValue(employee.getWorkId());
            HSSFCell cell3 = row.createCell(3);
            cell3.setCellStyle(dateCellStyle);
            cell3.setCellValue(employee.getBirthday());
            row.createCell(4).setCellValue(employee.getGender());
            row.createCell(5).setCellValue(employee.getWedlock());
            row.createCell(6).setCellValue(employee.getNation().getName());
            row.createCell(7).setCellValue(employee.getNativePlace());
            row.createCell(8).setCellValue(employee.getPoliticsStatus().getName());
            row.createCell(9).setCellValue(employee.getEmail());
            row.createCell(10).setCellValue(employee.getPhone());
            row.createCell(11).setCellValue(employee.getAddress());
            row.createCell(12).setCellValue(employee.getDepartment().getName());
            row.createCell(13).setCellValue(employee.getPosition().getName());
            row.createCell(14).setCellValue(employee.getJobLevel().getName());
            row.createCell(15).setCellValue(employee.getEngageForm());
            HSSFCell cell16 = row.createCell(16);
            cell16.setCellStyle(dateCellStyle);
            cell16.setCellValue(employee.getBeginDate());
            HSSFCell cell17 = row.createCell(17);
            cell17.setCellStyle(dateCellStyle);
            cell17.setCellValue(employee.getConversionTime());
            HSSFCell cell18 = row.createCell(18);
            cell18.setCellStyle(dateCellStyle);
            cell18.setCellValue(employee.getBeginContract());
            HSSFCell cell19 = row.createCell(19);
            cell19.setCellValue(employee.getEndContract());
            cell19.setCellStyle(dateCellStyle);
            row.createCell(20).setCellValue(employee.getContractTerm() == null ? 0 : employee.getContractTerm());
            row.createCell(21).setCellValue(employee.getTiptopDegree());
            row.createCell(22).setCellValue(employee.getSchool());
            row.createCell(23).setCellValue(employee.getSpecialty());
        }

        // 字节数组输出流
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders httpHeaders = new HttpHeaders();
        try {
            // 常规下载头设置
            httpHeaders.setContentDispositionFormData("attachment",new String("员工表.xls".getBytes("UTF-8"),"ISO-8859-1"));
            httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            // 转为流
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }


        return new ResponseEntity<byte[]>(baos.toByteArray(), httpHeaders, HttpStatus.CREATED) ;
    }
}
